source("../Rprofile.R", echo = FALSE) #load libraries
## 
## Attaching package: 'gplots'
## 
## The following object is masked from 'package:stats':
## 
##     lowess
## 
## 
## Attaching package: 'dplyr'
## 
## The following object is masked from 'package:stats':
## 
##     filter
## 
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
## 
## Loading required package: bitops
## 
## Attaching package: 'jsonlite'
## 
## The following object is masked from 'package:utils':
## 
##     View
SQL Crosstabs
source("../01 SQL Crosstabs/Crosstab.R", echo = TRUE) #load crosstabs
## 
## > dfrank <- data.frame(fromJSON(getURL(URLencode(gsub("\n", 
## +     " ", "129.152.144.84:5001/rest/native/?query=\n\"select TYPE, ZIP, PRICE, RANK() OV .... [TRUNCATED] 
## 
## > dfmax <- data.frame(fromJSON(getURL(URLencode(gsub("\n", 
## +     " ", "129.152.144.84:5001/rest/native/?query=\"SELECT TYPE, ZIP, PRICE, \nMAX(PRICE) .... [TRUNCATED] 
## 
## > dfnth <- data.frame(fromJSON(getURL(URLencode(gsub("\n", 
## +     " ", "129.152.144.84:5001/rest/native/?query=\"SELECT * FROM (SELECT TYPE, ZIP, PRIC .... [TRUNCATED] 
## 
## > dfcume <- data.frame(fromJSON(getURL(URLencode(gsub("\n", 
## +     " ", "129.152.144.84:5001/rest/native/?query=\"SELECT TYPE, ZIP, PRICE,  \n(100.0 * .... [TRUNCATED]
tbl_df(dfrank)
## Source: local data frame [985 x 4]
## 
##                    TYPE   ZIP  PRICE RANK
## 1  Condo                95603 260000    1
## 2  Condo                95608 250134    1
## 3  Condo                95608 182000    2
## 4  Condo                95608 139500    3
## 5  Condo                95621 116250    1
## 6  Condo                95621  69000    2
## 7  Condo                95624  71000    1
## 8  Condo                95628 142500    1
## 9  Condo                95630 240000    1
## 10 Condo                95648 188000    1
## ..                  ...   ...    ...  ...
tbl_df(dfmax)
## Source: local data frame [985 x 5]
## 
##                    TYPE   ZIP  PRICE MAX_PRICE CALCULATION
## 1  Condo                95603 260000    260000           0
## 2  Condo                95608 139500    250134      110634
## 3  Condo                95608 182000    250134       68134
## 4  Condo                95608 250134    250134           0
## 5  Condo                95621  69000    116250       47250
## 6  Condo                95621 116250    116250           0
## 7  Condo                95624  71000     71000           0
## 8  Condo                95628 142500    142500           0
## 9  Condo                95630 240000    240000           0
## 10 Condo                95648 138000    188000       50000
## ..                  ...   ...    ...       ...         ...
tbl_df(dfnth)
## Source: local data frame [66 x 4]
## 
##                    TYPE   ZIP  PRICE NTH
## 1  Condo                95608 139500   3
## 2  Condo                95678 212500   3
## 3  Condo                95758 133000   3
## 4  Condo                95825 107666   3
## 5  Condo                95826  90000   3
## 6  Condo                95834 120000   3
## 7  Condo                95835 152000   3
## 8  Condo                95841  77000   3
## 9  Condo                95842  82732   3
## 10 Multi-Family         95828 159900   3
## ..                  ...   ...    ... ...
tbl_df(dfcume)
## Source: local data frame [985 x 4]
## 
##                    TYPE   ZIP  PRICE CALCULATION
## 1  Condo                95603 260000   100.00000
## 2  Condo                95608 139500    55.77011
## 3  Condo                95608 182000    72.76100
## 4  Condo                95608 250134   100.00000
## 5  Condo                95621  69000    59.35484
## 6  Condo                95621 116250   100.00000
## 7  Condo                95624  71000   100.00000
## 8  Condo                95628 142500   100.00000
## 9  Condo                95630 240000   100.00000
## 10 Condo                95648 138000    73.40426
## ..                  ...   ...    ...         ...
Tableau Crosstabs
Rank Crosstab shows the ranking of sum of price for each price broken down by type of residence and zipcode

MaxDifference Crosstab shows the max price and the difference between max price and each price broken down by type of residence and zipcode

Nth Crosstab shows the nth value of price for each zipcode broken down by type of residence and zipcode

Cume_dist Crosstab shows the percentage of the each price over max price broken down by type of residence and zipcode